import paramiko as pm
import time as tm
import datetime
import pymysql

# mysql数据库连接信息
db = pymysql.connect(host='10.13.110.253', user='root', password='Kc@123456', database='kcgbs')
cursor = db.cursor()
# 服务器的连接信息
user_name = 'root'
pass_word = 'kc@123456'
url = '10.13.110.253'
port = 22

# 缺少视频的开始时间和结束时间
startTime = 1660875000
endTime = 1660883820

tran = pm.Transport((url, port))
tran.connect(username=user_name, password=pass_word)
sftp = pm.SFTPClient.from_transport(tran)

if __name__ == '__main__':
    getDeviceSql = "select dc.deviceId,dc.channelId,dc.name,d.mediaServerId,dc.streamId,CONCAT('record_info_',dc.deviceId,'_',dc.channelId) from device d inner join device_channel dc on d.deviceId=dc.deviceId"
    cursor.execute(getDeviceSql)
    fetchall = cursor.fetchall()
    for dbPath in fetchall:
        path1 = dbPath[3][-2:]
        file = '/iscsi-dev-'
        if path1 == '11':
            file = file+'13T-'+path1+'/www/record/rtp/'
        else:
            file = file+'64T-'+path1+'/www/record/rtp/'
        tableNamne = dbPath[5]
        filePath = file+dbPath[4]
        filesAttrs = sftp.listdir_attr(filePath)

        for files in filesAttrs:
             if files.st_mode == 16877:
                 filesAttr = sftp.listdir_attr(filePath + "/" + files.filename)
                 for file in filesAttr:
                     sr_time = files.filename + " " + file.filename.split('.')[0].replace('-', ':')
                     if len(sr_time) > 11:
                         it_time = datetime.datetime.strptime(sr_time, "%Y-%m-%d %H:%M:%S")
                         # 时间戳
                         time_long = int(tm.mktime(it_time.timetuple()))
                         if startTime < time_long < endTime:
                             # 文件大小
                             file_size = file.st_size
                             # stream + mediaServerId + fileName
                             # 'Hello {0}! My name is {1}.'.format('World', 'Python猫')
                             vidoe_one = "select count(*) from {0} where stream = '{1}' and mediaServerId = '{2}' and fileName = '{3}'".format(tableNamne,dbPath[4],dbPath[3], file.filename)
                             cursor.execute(vidoe_one);
                             fetchall = cursor.fetchall()
                             count_ = fetchall[0][0]
                             if count_ == 0:
                                sqlstart = "INSERT INTO "+tableNamne+" (app, vhost, stream, mediaServerId, startTime, timeLen, fileSize, url, fileName, filePath, folder) VALUES ('rtp', '__defaultVhost__', '"+dbPath[4]+"', '"+dbPath[3]+"',"
                                file_sql = sqlstart + str(time_long) + ' , ' + str(900) + ' , ' + str(file_size)
                                file_path = files.filename + "/" + file.filename
                                inputsql = file_sql + ", '/record/rtp/"+dbPath[4]+"/" + file_path + "' , '" + file.filename + "', '/zlmediakit/www/record/rtp/"+dbPath[4]+"/" + file_path + "' , '/zlmediakit/www/record/rtp/"+dbPath[4]+"/');"
                                print(inputsql)



